# -*- coding: utf-8 -*- 
"""
========================================================================================================================
@project : my-sanic
@file: mySqlite
@Author: mengying
@email: 652044581@qq.com
@date: 2023/3/24 16:55
@desc: sqlite3本地数据库的操作类
========================================================================================================================
"""
import json
import sqlite3
from threading import Lock
from typing import Dict


class SingletonMeta(type):
    """元类单例"""
    __instance = None
    __lock = Lock()

    def __call__(cls, *args, **kwargs):
        with cls.__lock:
            new = kwargs.pop('new', None)
            if new is True:
                return super().__call__(*args, **kwargs)
            if not cls.__instance:
                cls.__instance = super().__call__(*args, **kwargs)
        return cls.__instance


class Database(metaclass=SingletonMeta):
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def create_table(self, table_name, columns):
        self.cursor.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})")
        self.conn.commit()

    def insert_data(self, table_name, data):
        if isinstance(data, dict):
            fields = self.query_field(table_name)
            transform = [data.get(field[1], None) for field in fields]
        else:
            transform = data

        placeholders = ', '.join(['?' for _ in range(len(data))])
        self.cursor.execute(f"INSERT INTO {table_name} VALUES ({placeholders})", transform)
        self.conn.commit()

    def select_data(self, table_name, columns=None, condition=None):
        if columns is None:
            columns = '*'
        query = f"SELECT {columns} FROM {table_name}"
        if condition is not None:
            query += f" WHERE {self.convert(condition)}"
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [col[0] for col in self.cursor.description]
        results = []
        for row in rows:
            results.append(dict(zip(columns, row)))
        return json.dumps(results)

    def update_data(self, table_name, data, condition: Dict):
        placeholders = ', '.join([f"{key} = ?" for key in data.keys()])
        self.cursor.execute(f"UPDATE {table_name} SET {placeholders} WHERE {self.convert(condition)}",
                            tuple(data.values()))
        self.conn.commit()

    def delete_data(self, table_name, condition: Dict):
        self.cursor.execute(f"DELETE FROM {table_name} WHERE {self.convert(condition)}")
        self.conn.commit()

    def query_field(self, table_name):
        self.cursor.execute(f"PRAGMA table_info([{table_name}])")
        return self.cursor.fetchall()

    def convert(self, condition: Dict):
        init_string = "1=1"
        for k, v in condition.items():
            if isinstance(v, str):
                init_string += " and %s='%s'" % (k, v)
            else:
                init_string += " and %s=%s" % (k, v)
        return init_string


if __name__ == '__main__':
    # Example usage
    db = Database('example.db')
    db.create_table('users', 'id INTEGER PRIMARY KEY, name TEXT, age INTEGER')
    db.insert_data('users', (12, 'Bob', 30))
    db.insert_data('users', {"id": 143, "name": "Bob", "age": 32})
    db.update_data('users', {'age': 32}, {"name": 'Bob'})
    db.delete_data('users', {"name": 'Alice'})
    print(db.select_data('users'))  # Output: [(2, 'Bob', 31)]
